1 Imports System.Data.SqlClient
2 Imports System.IO
3
4 Public Class frmSupplier
5 Dim s As String
6 Dim Photoname As String = ""
7 Dim IsImageChanged As Boolean = False
8 Sub Reset()
9 txtSupplierName.Text = ""
10 txtAddress.Text = ""
11 txtRemarks.Text = ""
12 txtSupplierName.Text = ""
13 txtSupplierID.Text = ""
14 txtContactNo.Text = ""
15 txtEmailID.Text = ""
16 cmbState.Text = ""
17 txtZipCode.Text = ""
18 txtCity.Text = ""
19 txtSupplierName.Focus()
20 btnSave.Enabled = True
21 btnUpdate.Enabled = False
22 btnDelete.Enabled = False
23 auto()
24 End Sub
25 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
26 Me.Close()
27 End Sub
28 Private Function GenerateID() As String
29 con = New SqlConnection(cs)
30 Dim value As String = "0000"
31 Try
32 ' Fetch the latest ID from the database
33 con.Open()
34 cmd = New SqlCommand("SELECT TOP 1 ID FROM Supplier ORDER BY ID DESC", con)
35 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
36 If rdr.HasRows Then
37 rdr.Read()
38 value = rdr.Item("ID")
39 End If
40 rdr.Close()
41 ' Increase the ID by 1
42 value += 1
43 ' Because incrementing a string with an integer removes 0's
44 ' we need to replace them. If necessary.
45 If value <= 9 Then 'Value is between 0 and 10
46 value = "000" & value
47 ElseIf value <= 99 Then 'Value is between 9 and 100
48 value = "00" & value
49 ElseIf value <= 999 Then 'Value is between 999 and 1000
50 value = "0" & value
51 End If
52 Catch ex As Exception
53 ' If an error occurs, check the connection state and close it if necessary.
54 If con.State = ConnectionState.Open Then
55 con.Close()
56 End If
57 value = "0000"
58 End Try
59 Return value
60 End Function
61 Sub auto()
62 Try
63 txtID.Text = GenerateID()
64 txtSupplierID.Text = "S-" + GenerateID()
65 Catch ex As Exception
66 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
67 End Try
68 End Sub
69 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
70 If Len(Trim(txtSupplierName.Text)) = 0 Then
71 MessageBox.Show("Please enter supplier name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
72 txtSupplierName.Focus()
73 Exit Sub
74 End If
75 If Len(Trim(txtAddress.Text)) = 0 Then
76 MessageBox.Show("Please Enter Address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
77 txtAddress.Focus()
78 Exit Sub
79 End If
80 If Len(Trim(txtCity.Text)) = 0 Then
81 MessageBox.Show("Please Enter City", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
82 txtCity.Focus()
83 Exit Sub
84 End If
85 If Len(Trim(txtContactNo.Text)) = 0 Then
86 MessageBox.Show("Please Enter Contact No.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
87 txtContactNo.Focus()
88 Exit Sub
89 End If
90
91 Try
92 con = New SqlConnection(cs)
93 con.Open()
94 Dim ct As String = "select RTRIM(ContactNo) from Supplier where ContactNo=@d1"
95 cmd = New SqlCommand(ct)
96 cmd.Parameters.AddWithValue("@d1", txtContactNo.Text)
97 cmd.Connection = con
98 rdr = cmd.ExecuteReader()
99
100 If rdr.Read() Then
101 MessageBox.Show("Entered contact no. is already registered", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
102 If (rdr IsNot Nothing) Then
103 rdr.Close()
104 End If
105 Return
106 End If
107 con.Close()
108 con = New SqlConnection(cs)
109 con.Open()
110 Dim cb As String = "insert into Supplier(ID, SupplierID, [Name], Address, City, ContactNo, EmailID,Remarks,State,ZipCode) VALUES (@d1,@d2,@d3,@d5,@d6,@d7,@d8,@d9,@d10,@d11)"
111 cmd = New SqlCommand(cb)
112 cmd.Parameters.AddWithValue("@d1", txtID.Text)
113 cmd.Parameters.AddWithValue("@d2", txtSupplierID.Text)
114 cmd.Parameters.AddWithValue("@d3", txtSupplierName.Text)
115 cmd.Parameters.AddWithValue("@d5", txtAddress.Text)
116 cmd.Parameters.AddWithValue("@d6", txtCity.Text)
117 cmd.Parameters.AddWithValue("@d7", txtContactNo.Text)
118 cmd.Parameters.AddWithValue("@d8", txtEmailID.Text)
119 cmd.Parameters.AddWithValue("@d9", txtRemarks.Text)
120 cmd.Parameters.AddWithValue("@d10", cmbState.Text)
121 cmd.Parameters.AddWithValue("@d11", txtZipCode.Text)
122 cmd.Connection = con
123 cmd.ExecuteNonQuery()
124 LogFunc(lblUser.Text, "added the new supplier having supplier id '" & txtSupplierID.Text & "'")
125 MessageBox.Show("Successfully saved", "Supplier Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
126 btnSave.Enabled = False
127 fillState()
128 con.Close()
129 Catch ex As Exception
130 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
131 End Try
132 End Sub
133
134 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
135 Try
136 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
137 DeleteRecord()
138 End If
139 Catch ex As Exception
140 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
141 End Try
142 End Sub
143 Private Sub DeleteRecord()
144 Try
145 Dim RowsAffected As Integer = 0
146 con = New SqlConnection(cs)
147 con.Open()
148 Dim cl As String = "SELECT Supplier.ID FROM Supplier INNER JOIN Stock ON Supplier.ID = Stock.SupplierID where Supplier.ID=@d1"
149 cmd = New SqlCommand(cl)
150 cmd.Connection = con
151 cmd.Parameters.AddWithValue("@d1", txtID.Text)
152 rdr = cmd.ExecuteReader()
153 If rdr.Read Then
154 MessageBox.Show("Unable to delete..Already in use in Stock Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
155 If Not rdr Is Nothing Then
156 rdr.Close()
157 End If
158 Exit Sub
159 End If
160 con.Close()
161 con = New SqlConnection(cs)
162 con.Open()
163 Dim cq As String = "delete from Supplier where ID =" & txtID.Text & ""
164 cmd = New SqlCommand(cq)
165 cmd.Connection = con
166 RowsAffected = cmd.ExecuteNonQuery()
167 If RowsAffected > 0 Then
168 LogFunc(lblUser.Text, "deleted the supplier record having supplier id '" & txtSupplierID.Text & "'")
169 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
170 Reset()
171 fillState()
172 Else
173 MessageBox.Show("No record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
174 Reset()
175 If con.State = ConnectionState.Open Then
176 con.Close()
177 End If
178 con.Close()
179 End If
180 Catch ex As Exception
181 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
182 End Try
183 End Sub
184
185 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
186 If Len(Trim(txtSupplierName.Text)) = 0 Then
187 MessageBox.Show("Please enter supplier name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
188 txtSupplierName.Focus()
189 Exit Sub
190 End If
191 If Len(Trim(txtAddress.Text)) = 0 Then
192 MessageBox.Show("Please Enter Address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
193 txtAddress.Focus()
194 Exit Sub
195 End If
196 If Len(Trim(txtCity.Text)) = 0 Then
197 MessageBox.Show("Please Enter City", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
198 txtCity.Focus()
199 Exit Sub
200 End If
201 If cmbState.Text = "" Then
202 MessageBox.Show("Please enter state", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
203 cmbState.Focus()
204 Return
205 End If
206 If Len(Trim(txtContactNo.Text)) = 0 Then
207 MessageBox.Show("Please Enter Contact No.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
208 txtContactNo.Focus()
209 Exit Sub
210 End If
211
212 Try
213 con = New SqlConnection(cs)
214 con.Open()
215 Dim cb As String = "update supplier set SupplierID=@d2,[Name]=@d3, Address=@d5,City=@d6, ContactNo=@d7, EmailID=@d8,Remarks=@d9,State=@d10,ZipCode=@d11 where ID=@d1"
216 cmd = New SqlCommand(cb)
217
218 cmd.Parameters.AddWithValue("@d2", txtSupplierID.Text)
219 cmd.Parameters.AddWithValue("@d3", txtSupplierName.Text)
220 cmd.Parameters.AddWithValue("@d5", txtAddress.Text)
221 cmd.Parameters.AddWithValue("@d6", txtCity.Text)
222 cmd.Parameters.AddWithValue("@d7", txtContactNo.Text)
223 cmd.Parameters.AddWithValue("@d8", txtEmailID.Text)
224 cmd.Parameters.AddWithValue("@d9", txtRemarks.Text)
225 cmd.Parameters.AddWithValue("@d10", cmbState.Text)
226 cmd.Parameters.AddWithValue("@d11", txtZipCode.Text)
227 cmd.Connection = con
228 cmd.Parameters.AddWithValue("@d1", txtID.Text)
229 cmd.ExecuteNonQuery()
230 LogFunc(lblUser.Text, "updated the supplier having supplier id '" & txtSupplierID.Text & "'")
231 MessageBox.Show("Successfully updated", "Supplier Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
232 btnUpdate.Enabled = False
233 fillState()
234 con.Close()
235 Catch ex As Exception
236 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
237 End Try
238 End Sub
239
240 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
241 Reset()
242 End Sub
243
244
245 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
246 Dim frm As New frmSupplierRecord
247 frm.lblSet.Text = "Supplier Entry"
248 frm.Getdata()
249 frm.ShowDialog()
250 End Sub
251
252 Private Sub frmSupplier_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
253 fillState()
254 End Sub
255 Sub fillState()
256 Try
257 con = New SqlConnection(cs)
258 con.Open()
259 adp = New SqlDataAdapter()
260 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(State) FROM Supplier order by 1", con)
261 ds = New DataSet("ds")
262 adp.Fill(ds)
263 dtable = ds.Tables(0)
264 cmbState.Items.Clear()
265 For Each drow As DataRow In dtable.Rows
266 cmbState.Items.Add(drow(0).ToString())
267 Next
268 Catch ex As Exception
269 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
270 End Try
271 End Sub
272 Private Sub cmbState_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbState.Format
273 If (e.DesiredType Is GetType(String)) Then
274 e.Value = e.Value.ToString.Trim
275 End If
276 End Sub
277 End Class
